package com.turing.manage.vip;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.turing.utils.ExcelUtils;

@SuppressWarnings("serial")
public class VipServlet extends HttpServlet {
	
	VipService examineeService = new VipService();

	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		try {
			request.setCharacterEncoding("utf-8");
			response.setCharacterEncoding("utf-8");
			String method = request.getParameter("method");
			
			if ("query".equals(method))
			{
				this.query(request,response);
			}
			else if ("delete".equals(method)) {
				this.delete(request,response);
			}
			else if ("export".equals(method)) {
				this.export(request,response);
			}
			else if ("importExcel".equals(method)) {
				this.importExcel(request,response);
			}
		} catch (ClassNotFoundException | SQLException e) {
			System.out.println("1.发送的异常信息为:"+e.getMessage());
			System.out.println("2.发送异常的内存地址为:"+e.getStackTrace());
			System.out.println("3.转向到错误处理页面");
			request.setAttribute("errorMsg",e.getMessage());
			request.getRequestDispatcher("/manage/error.jsp").forward(request, response);
		}
	}

	
	
	
	
	/**
	 * @desc  导入excel
	 * @param request
	 * @param response
	 * @throws IOException 
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	private void importExcel(HttpServletRequest request, HttpServletResponse response) throws IOException, ClassNotFoundException, SQLException 
	{
		String fed=request.getParameter("fed");
		System.out.println("fed:"+fed);
		Workbook  wb = null;//备注：导入的包一定都是ss下的
		Sheet  sheet;
		Row row;
		
		File file=new File(fed);
		FileInputStream fileInputStream=new FileInputStream(file);
		
		 if (file.isFile() && file.exists()) 
		 {
			//System.out.println(excel.getName());
			String excelName = file.getName();//获取当前文件的名字
			int st = excelName.lastIndexOf("."); //截取后缀开始的前一位
			System.out.println(st); 
			String suffix = (String) excelName.subSequence(st+1, excelName.length());//截取文件的后缀
			System.out.println(suffix);
			if (suffix.equals("xlsx"))
			{
			  System.out.println("走2007的解析......");	
			  wb=new XSSFWorkbook(fileInputStream);
			}
			else if (suffix.equals("xls")) 
			{
			  System.out.println("走2003的解析......");	
	          wb=new HSSFWorkbook(fileInputStream);
			}
			else {
	            System.out.println("文件类型错误!");
	            System.exit(0);//退出当前程序
	        }
			 
		 }
		
		 sheet=wb.getSheetAt(0);  //获取当前excel工作簿中的第一个sheet
		 //获取当前sheet中有多少个行
		 int totalRowNums = sheet.getPhysicalNumberOfRows();
		 
		 for(int i =1;i<totalRowNums;i++)
		 {
			  row = sheet.getRow(i);//获取每一行
			 int totalCellNums = row.getPhysicalNumberOfCells();//获取每一行中有多少列
			 List<String> columnValuesList=new ArrayList<String>();
			 for(int k =0;k<totalCellNums;k++)
			 {
			 Cell cell = row.getCell(k);//获取每一列对象   
			 String cellValue = ExcelUtils.getFormatValue(cell);
			  //System.out.print(cellValue+"\t");
			 columnValuesList.add(k, cellValue);//将每一列上的值都转入到list容器中	 
			 }
			 examineeService.saveExamineeByImportExcel(columnValuesList);//每整完一行保存一次
			 //System.out.println("开始读取下一行数据:");
		 }
		 
		//重定向
			response.sendRedirect(request.getContextPath()+"/manage/vip.do?method=query");	 
		}





	private void export(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, IOException {
		System.out.println("examineeServlet-->export()");
		 //调用excel处理的工具类并设置本次导出excel的相关信息
		   
		ExcelUtils.settings(response, "vip详细信息的工作簿.xls");//改名字
	    //0.接值
		//就是说  拿到 想要导出信息的 (下拉框中的内容) 班级的ID和   搜索框中的值
		String key = request.getParameter("ky");
		String classs_id=request.getParameter("clasId");
		
		System.out.println("----->"+key);
		System.out.println("----->"+classs_id);
		
		
		//1.调用M层获取到数据   及执行sql语句
		 List<Map<String, Object>> list=examineeService.queryExamineeAll(classs_id, key);
		
		//2.利用poi实现将获取到的数据写入到excel中
		 //2.1初始化poi的核心类
		 HSSFWorkbook workbook=new HSSFWorkbook();
		 String sheetname="考生详细信息的sheet";

		 HSSFSheet sheet = workbook.createSheet(sheetname);
		 
		 //2.2调用poi中的相关API把数据写入到excel中
		 HSSFRow row = sheet.createRow(0);//创建第一行，即表头
		 String [] tableTop={"vipID", "姓名", "年龄", "性别","余额","使用时间","状态","专业","身份证号","日期" };
		 //像第一行中添加数据（创建表头）
		 for(int i =0;i<tableTop.length;i++)
		 {
			 row.createCell(i).setCellValue(tableTop[i]);
		 }
		 
		 
		 //从第二行开始向里面写入数据
		 String[] columnName = { "svip_id","svip_name","svip_age","svip_sex","svip_money","svip_time","svip_style"};
		 for(int i =0;i<list.size();i++)
		 {
		    HSSFRow row2 = sheet.createRow(i+1);
			
		    Map<String, Object> map = list.get(i);//从list中取出数据 
			for(int k=0;k<columnName.length;k++)//将获取到的每一个map写入到一个新的row2对象中（一个row2对象即是excel表格中的一行）
			{
				sheet.autoSizeColumn(k, true);//设定表格进行自动适配，但是对中文不是特别友好
				row2.createCell(k).setCellValue((String)map.get(columnName[k]));
			}
		 }
		 ExcelUtils.setColumnAutoAdapter(sheet, list.size()+1);
		 //2.3保存
		 OutputStream outputStream = response.getOutputStream();
		 workbook.write(outputStream);
		 outputStream.close();
	}





	private void delete(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, FileNotFoundException, SQLException, IOException {
				String[] delids = request.getParameterValues("delIdArray");
				for (String everydelids : delids) {
					System.out.println(everydelids);
				}
				//2.调用M层中的方法实现删除
				examineeService.deleteExamineeByIds(delids);
				//3.重定向
				response.sendRedirect(request.getContextPath()+"/manage/examinee.do?method=query");
		
	}




	
/**
 *  desc 查询全部
 * @param request
 * @param response
 * @throws ClassNotFoundException
 * @throws SQLException
 * @throws ServletException
 * @throws IOException
 */
	private void query(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException {
		
		//1.接值
		String key = request.getParameter("key");
		String option_list=request.getParameter("option_list");
		
		
		//2.调用M层进行查询
		//2.1查询考生信息
		List<Map<String, Object>> list=examineeService.queryExamineeAll(option_list, key);
	
		
		//3.将查询结果和查询条件存放在request作用域中
		
		request.setAttribute("list", list);
	    request.setAttribute("key", key);
	   	request.setAttribute("option_list", option_list);
		
		
		request.getRequestDispatcher("/manage/svip/list.jsp").forward(request, response);
		
		
	}
	
	
}
